<<<<<<< HEAD:backend/init_db.py
"""
数据库初始化脚本
"""
import logging
from sqlalchemy import text
from database import engine, test_connection, Base
from models import *  # 导入所有模型以创建表

# 配置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

def init_database():
    """初始化数据库"""
    try:
        # 测试数据库连接
        if not test_connection():
            logger.error("数据库连接失败，无法初始化")
            return False
        
        logger.info("开始创建数据库表...")
        
        # 创建所有表
        Base.metadata.create_all(bind=engine)
        
        logger.info("数据库表创建成功")
        
        # 检查表是否存在
        with engine.connect() as conn:
            result = conn.execute(text("SHOW TABLES"))
            tables = [row[0] for row in result]
            logger.info(f"当前数据库中的表: {tables}")
        
        return True
        
    except Exception as e:
        logger.error(f"数据库初始化失败: {e}")
        return False

def check_tables():
    """检查数据库表状态"""
    try:
        with engine.connect() as conn:
            result = conn.execute(text("SHOW TABLES"))
            tables = [row[0] for row in result]
            
            logger.info("=== 数据库表状态检查 ===")
            logger.info(f"总表数量: {len(tables)}")
            
            for table in tables:
                # 检查表结构
                result = conn.execute(text(f"DESCRIBE {table}"))
                columns = [row[0] for row in result]
                logger.info(f"表 {table}: {len(columns)} 个字段")
            
            return tables
            
    except Exception as e:
        logger.error(f"检查表状态失败: {e}")
        return []

if __name__ == "__main__":
    logger.info("开始数据库初始化...")
    
    # 检查当前数据库状态
    tables = check_tables()
    
    if tables:
        logger.info("数据库已存在表结构，强制重新创建表结构")
        # 先禁用外键检查
        with engine.connect() as conn:
            conn.execute(text("SET FOREIGN_KEY_CHECKS = 0"))
            
            # 手动删除所有表，避免循环依赖问题
            for table in reversed(tables):  # 逆序删除，先删除依赖较少的表
                try:
                    conn.execute(text(f"DROP TABLE IF EXISTS {table}"))
                    logger.info(f"删除表: {table}")
                except Exception as e:
                    logger.warning(f"删除表 {table} 失败: {e}")
        
        # 重新启用外键检查
        with engine.connect() as conn:
            conn.execute(text("SET FOREIGN_KEY_CHECKS = 1"))
        
        logger.info("所有表已删除")
    
    # 创建所有表
    logger.info("开始创建表结构")
    init_database()
    
    logger.info("数据库初始化完成")
=======
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
数据库初始化脚本
用于创建数据库表和初始数据
"""

import os
import sys
from sqlalchemy import create_engine, text, MetaData
from sqlalchemy.exc import SQLAlchemyError
import logging

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[logging.StreamHandler()]
)
logger = logging.getLogger(__name__)

# 数据库连接配置
# 使用用户提供的密码
DB_CONFIG = {
    "host": "localhost",
    "user": "root",
    "password": "123456",  # 用户提供的密码
    "database": "trading",
    "port": 3306
}

# 构建数据库URL
DB_URL = f"mysql+pymysql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['database']}"

# 表结构SQL语句
CREATE_TABLES_SQL = """
-- 用户表
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    avatar VARCHAR(255),
    bio TEXT,
    reputation FLOAT DEFAULT 0.0,
    is_active BOOLEAN DEFAULT TRUE,
    is_admin BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 商品分类表
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    parent_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 商品表
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    category_id INT,
    user_id INT NOT NULL,
    condition ENUM('new', 'like_new', 'good', 'acceptable', 'poor') DEFAULT 'good',
    is_active BOOLEAN DEFAULT TRUE,
    stock INT DEFAULT 1,
    images JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 交易表
CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    buyer_id INT NOT NULL,
    seller_id INT NOT NULL,
    quantity INT DEFAULT 1,
    total_amount DECIMAL(10, 2) NOT NULL,
    transaction_type ENUM('buy', 'sell', 'transfer') DEFAULT 'buy',
    status ENUM('pending', 'processing', 'completed', 'cancelled', 'refunded') DEFAULT 'pending',
    shipping_address TEXT,
    tracking_info VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (buyer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 钱包表
CREATE TABLE IF NOT EXISTS wallets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    balance DECIMAL(12, 2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 钱包交易记录表
CREATE TABLE IF NOT EXISTS wallet_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    wallet_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    transaction_type ENUM('deposit', 'withdraw', 'transfer_in', 'transfer_out', 'payment', 'refund') NOT NULL,
    status ENUM('pending', 'completed', 'failed') DEFAULT 'pending',
    description TEXT,
    transaction_reference VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (wallet_id) REFERENCES wallets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 消息表
CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sender_id INT NOT NULL,
    recipient_id INT NOT NULL,
    content TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    parent_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (recipient_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES messages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 评价表
CREATE TABLE IF NOT EXISTS reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    target_type ENUM('product', 'user', 'transaction') NOT NULL,
    target_id INT NOT NULL,
    user_id INT NOT NULL,
    rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    content TEXT,
    is_anonymous BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 评价回复表
CREATE TABLE IF NOT EXISTS review_replies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    review_id INT NOT NULL,
    user_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (review_id) REFERENCES reviews(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 通知表
CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('system', 'transaction', 'message', 'product', 'review') NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    related_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 索引优化
CREATE INDEX idx_products_user_id ON products(user_id);
CREATE INDEX idx_products_category_id ON products(category_id);
CREATE INDEX idx_products_is_active ON products(is_active);
CREATE INDEX idx_transactions_buyer_id ON transactions(buyer_id);
CREATE INDEX idx_transactions_seller_id ON transactions(seller_id);
CREATE INDEX idx_transactions_status ON transactions(status);
CREATE INDEX idx_messages_sender_id ON messages(sender_id);
CREATE INDEX idx_messages_recipient_id ON messages(recipient_id);
CREATE INDEX idx_messages_is_read ON messages(is_read);
CREATE INDEX idx_reviews_target ON reviews(target_type, target_id);
CREATE INDEX idx_notifications_user_id ON notifications(user_id);
CREATE INDEX idx_notifications_is_read ON notifications(is_read);
CREATE INDEX idx_wallet_transactions_wallet_id ON wallet_transactions(wallet_id);
CREATE INDEX idx_wallet_transactions_type ON wallet_transactions(transaction_type);
CREATE INDEX idx_wallet_transactions_status ON wallet_transactions(status);
"""

# 插入初始数据的SQL语句
INSERT_INITIAL_DATA_SQL = """
-- 插入初始分类数据
INSERT INTO categories (name, description) VALUES 
('电子产品', '各类电子设备、数码产品'),
('图书音像', '教材、课外书、音像制品'),
('服饰鞋包', '服装、鞋子、包包等'),
('生活用品', '各类日常生活用品'),
('运动户外', '运动器材、户外装备'),
('美妆护肤', '化妆品、护肤品'),
('其他', '其他类型的商品')
ON DUPLICATE KEY UPDATE name=name;

-- 插入默认管理员用户（密码: admin123，会在应用中进行哈希处理）
INSERT INTO users (username, email, password_hash, is_admin, is_active) VALUES 
('admin', 'admin@example.com', '$2b$12$EixZaYVK1fsbw1ZfbX3OXePaWxn96p36WQoeG6Lruj3vjPGga31lW', TRUE, TRUE)
ON DUPLICATE KEY UPDATE username=username;

-- 为所有用户创建钱包（如果不存在）
INSERT INTO wallets (user_id, balance) 
SELECT id, 0.00 FROM users u
WHERE NOT EXISTS (SELECT 1 FROM wallets w WHERE w.user_id = u.id);
"""

def create_database_if_not_exists():
    """创建数据库（如果不存在）"""
    # 先尝试直接连接到指定数据库
    try:
        engine = create_engine(DB_URL)
        with engine.connect() as conn:
            logger.info(f"数据库 {DB_CONFIG['database']} 已存在，可以直接连接")
            return True
    except SQLAlchemyError as e:
        logger.info(f"直接连接数据库失败，尝试创建数据库: {str(e)}")
        
    # 如果直接连接失败，尝试连接到MySQL服务器创建数据库
    try:
        # 先连接到MySQL服务器（不指定数据库）
        server_url = f"mysql+pymysql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@{DB_CONFIG['host']}:{DB_CONFIG['port']}/"
        engine = create_engine(server_url)
        
        with engine.connect() as conn:
            # 创建数据库
            conn.execute(text(f"CREATE DATABASE IF NOT EXISTS {DB_CONFIG['database']} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"))
            conn.execute(text(f"USE {DB_CONFIG['database']}"))
            logger.info(f"数据库 {DB_CONFIG['database']} 已创建或已存在")
            return True
    except SQLAlchemyError as e:
        logger.error(f"创建数据库失败: {str(e)}")
        logger.error("请确保MySQL服务已启动，并且用户名和密码正确")
        logger.error("提示：Windows上MySQL默认安装可能需要特定密码，常见密码包括'password'、'root'或空密码")
        return False
    finally:
        if 'engine' in locals():
            engine.dispose()

def create_tables():
    """创建数据库表"""
    engine = create_engine(DB_URL)
    
    # 首先删除已存在的表（按照外键依赖顺序）
    drop_tables = [
        "DROP TABLE IF EXISTS favorites;",
        "DROP TABLE IF EXISTS reviews;",
        "DROP TABLE IF EXISTS transactions;",
        "DROP TABLE IF EXISTS products;",
        "DROP TABLE IF EXISTS categories;",
        "DROP TABLE IF EXISTS wallets;",
        "DROP TABLE IF EXISTS messages;",
        "DROP TABLE IF EXISTS notifications;",
        "DROP TABLE IF EXISTS users;"
    ]
    
    # 分开定义每个表的创建语句，避免一次执行太多SQL
    tables_sql = [
        # 用户表
        """
        CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(50) NOT NULL UNIQUE,
            email VARCHAR(100) NOT NULL UNIQUE,
            password_hash VARCHAR(255) NOT NULL,
            phone VARCHAR(20),
            avatar VARCHAR(255),
            bio TEXT,
            reputation FLOAT DEFAULT 0.0,
            is_active BOOLEAN DEFAULT TRUE,
            is_admin BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """,
        # 商品分类表
        """
        CREATE TABLE IF NOT EXISTS categories (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL UNIQUE,
            description TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """,
        # 商品表
        """
        CREATE TABLE IF NOT EXISTS products (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            category_id INT NOT NULL,
            title VARCHAR(200) NOT NULL,
            description TEXT NOT NULL,
            price DECIMAL(10, 2) NOT NULL,
            images JSON,
            is_active BOOLEAN DEFAULT TRUE,
            view_count INT DEFAULT 0,
            favorite_count INT DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """,
        # 钱包表
        """
        CREATE TABLE IF NOT EXISTS wallets (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL UNIQUE,
            balance DECIMAL(12, 2) DEFAULT 0.00,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """,
        # 交易表
        """
        CREATE TABLE IF NOT EXISTS transactions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            product_id INT NOT NULL,
            buyer_id INT NOT NULL,
            seller_id INT NOT NULL,
            amount DECIMAL(10, 2) NOT NULL,
            status ENUM('pending', 'completed', 'canceled', 'refunded') DEFAULT 'pending',
            payment_method VARCHAR(50),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
            FOREIGN KEY (buyer_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """,
        # 消息表
        """
        CREATE TABLE IF NOT EXISTS messages (
            id INT AUTO_INCREMENT PRIMARY KEY,
            sender_id INT NOT NULL,
            recipient_id INT NOT NULL,
            content TEXT NOT NULL,
            is_read BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (recipient_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """,
        # 评价表
        """
        CREATE TABLE IF NOT EXISTS reviews (
            id INT AUTO_INCREMENT PRIMARY KEY,
            target_type ENUM('product', 'transaction', 'user') NOT NULL,
            target_id INT NOT NULL,
            user_id INT NOT NULL,
            rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
            comment TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """,
        # 收藏表
        """
        CREATE TABLE IF NOT EXISTS favorites (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            product_id INT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY unique_favorite (user_id, product_id),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """,
        # 通知表
        """
        CREATE TABLE IF NOT EXISTS notifications (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            type ENUM('system', 'transaction', 'message', 'product', 'review') NOT NULL,
            title VARCHAR(200) NOT NULL,
            content TEXT NOT NULL,
            is_read BOOLEAN DEFAULT FALSE,
            related_id INT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        """
    ]
    
    try:
        with engine.connect() as conn:
            # 首先删除旧表
            for drop_sql in drop_tables:
                try:
                    conn.execute(text(drop_sql))
                    conn.commit()
                    logger.info(f"成功删除旧表")
                except Exception as e:
                    logger.warning(f"删除旧表失败: {str(e)}")
                    # 继续尝试删除下一个表
            
            # 逐个执行表创建
            for i, sql in enumerate(tables_sql):
                try:
                    conn.execute(text(sql))
                    conn.commit()
                    logger.info(f"表 {i+1} 创建成功")
                except Exception as e:
                    logger.warning(f"表 {i+1} 创建失败: {str(e)}")
                    # 继续尝试创建下一个表
            
            logger.info("表创建过程完成")
            return True
    except SQLAlchemyError as e:
        logger.error(f"创建表过程中发生错误: {str(e)}")
        return False
    finally:
        engine.dispose()

def insert_initial_data():
    """插入初始数据"""
    engine = create_engine(DB_URL)
    
    # 分开执行每个插入语句，避免SQL语法错误
    initial_data_sql = [
        # 插入初始分类数据
        """
        INSERT IGNORE INTO categories (name, description) VALUES
        ('电子产品', '各类电子设备、数码产品'),
        ('图书音像', '教材、课外书、音像制品'),
        ('服饰鞋包', '服装、鞋子、包包等'),
        ('生活用品', '各类日常生活用品'),
        ('运动户外', '运动器材、户外装备'),
        ('美妆护肤', '化妆品、护肤品'),
        ('其他', '其他类型的商品');
        """,
        # 插入默认管理员用户（密码: admin123）
        """
        INSERT IGNORE INTO users (username, email, password_hash) VALUES
        ('admin', 'admin@example.com', '$2b$12$EixZaYVK1fsbw1ZfbX3OXePaWxn96p36WQoeG6Lruj3vjPGga31lW');
        """,
        # 为所有用户创建钱包（如果不存在）
        """
        INSERT IGNORE INTO wallets (user_id, balance)
        SELECT id, 0.00 FROM users u
        WHERE NOT EXISTS (SELECT 1 FROM wallets w WHERE w.user_id = u.id);
        """
    ]
    
    try:
        with engine.connect() as conn:
            # 逐个执行插入语句
            for i, sql in enumerate(initial_data_sql):
                try:
                    conn.execute(text(sql))
                    conn.commit()
                    logger.info(f"初始数据 {i+1} 插入成功")
                except Exception as e:
                    logger.warning(f"初始数据 {i+1} 插入失败: {str(e)}")
                    # 继续尝试下一个插入
            
            logger.info("初始数据插入过程完成")
            return True
    except SQLAlchemyError as e:
        logger.error(f"插入初始数据过程中发生错误: {str(e)}")
        return False
    finally:
        engine.dispose()

def check_connection():
    """检查数据库连接"""
    engine = create_engine(DB_URL)
    
    try:
        with engine.connect():
            logger.info("数据库连接成功")
            return True
    except SQLAlchemyError as e:
        logger.error(f"数据库连接失败: {str(e)}")
        return False
    finally:
        engine.dispose()

def main():
    """主函数"""
    logger.info("开始初始化数据库...")
    
    # 创建数据库（如果不存在）
    if not create_database_if_not_exists():
        logger.error("初始化数据库失败，无法创建数据库")
        sys.exit(1)
    
    # 检查连接
    if not check_connection():
        logger.error("初始化数据库失败，无法连接到数据库")
        sys.exit(1)
    
    # 创建表
    if not create_tables():
        logger.error("初始化数据库失败，无法创建表")
        sys.exit(1)
    
    # 插入初始数据
    if not insert_initial_data():
        logger.warning("表创建成功，但初始数据插入失败，可能是数据已存在")
    
    logger.info("数据库初始化完成！")

if __name__ == "__main__":
    main()
>>>>>>> eaf65a806b0d4eb1d6c3dbae23b4acad5fd00f21:backend_python/init_db.py
